package com.hongdee.insurance.company.service.impl;

import com.hongdee.base.entity.BaseEntity;
import com.hongdee.base.imp.vo.ExcelSchemePropertyVo;
import com.hongdee.base.imp.vo.ExcelSchemeVo;
import com.hongdee.base.util.EntityFactory;
import com.hongdee.common.dao.IImpHibernateDao;
import com.hongdee.common.util.StringUtil;
import com.hongdee.common.util.TokenUtil;
import com.hongdee.insurance.Constant;
import com.hongdee.insurance.company.dao.ITinsuranceCompanyBillDao;
import com.hongdee.insurance.company.entity.*;
import com.hongdee.insurance.company.service.ITInsuranceCompanyBillService;
import com.hongdee.insurance.company.service.ITInsuranceCompanyService;
import com.hongdee.poi.ExcelRowContentCallback;
import com.hongdee.poi.ExcelWorkSheetRowCallbackHandler;
import com.hongdee.poi.PoiExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import java.util.*;

/**
 * @author 王帅
 * @className TInsuranceCompanyBillServiceImpl
 * @type
 * @description 保险公司对账单实现
 * @path com.hongdee.insurance.company.service.impl
 * @create 2017/6/14 9:06
 **/
@Service
public class TInsuranceCompanyBillServiceImpl implements ITInsuranceCompanyBillService {
    private static final String TABLE_NAME = "T_EXCEL_SCHEME";
    @Autowired
    private ITinsuranceCompanyBillDao billDao;
    @Autowired
    private ITInsuranceCompanyService companyService;

    /**
     * 新增对账单信息
     * @param data
     * @param uid
     * @return
     * @throws Exception
     */
    @Override
    public TInsuranceCompanyBill save(TInsuranceCompanyBill data, String uid) throws Exception {
        if(data.getInsuranceCompanyId().equals("")){
            throw new Exception("请选择保险公司!!");
        }
        if("".equals(StringUtil.formatEmpty(data.getBlanceStartDate()))){
            throw new Exception("请选择结算开始时间!!");
        }
        if("".equals(StringUtil.formatEmpty(data.getBlanceStopDate()))){
            throw new Exception("请选择结算结束时间!!");
        }
        if("".equals(StringUtil.formatEmpty(data.getInTax()))){
            throw new Exception("请选择保单保费中是否包含税!!");
        }
        if(!"".equals(StringUtil.formatEmpty(data.getInTax()))){
            //如果保险公司提供的保单明细不包含税金，则需要提供税金的计算额度百分比
            if(String.valueOf(Constant.BILL_IN_TAX.ex.ordinal()).equals(StringUtil.formatEmpty(data.getInTax()))) {
                if("".equals(StringUtil.formatEmpty(data.getTax()))){
                    throw new Exception("请输入保单保费税额!!");
                }
            }
        }
        data.setCompanyId(TokenUtil.getCompanyId(uid));
        data.setCreateTime(System.currentTimeMillis());
        data.setCreateUser(uid);
        data.setBlanceType(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.init.ordinal()));
        data.setTotalPoundageAmount("0");
        data.setBlancePoundageAmount("0");
        data.setInsuranceTotal("0");
        data.setAdjustPrice("0");

        return (TInsuranceCompanyBill) billDao.saveOrUpdate(data);
    }

    /**
     * 对账单分页查询
     * @param data
     * @param uid
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map page(Map data,String uid,String start,String limit) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append(" select bill.id,DATE_FORMAT(FROM_UNIXTIME(bill.create_time/1000),'%Y-%m-%d') create_time, " +
                " bill.create_user, bill.update_time, bill.update_user, bill.blance_date, bill.blance_type," +
                " bill.company_id, bill.insurance_commission, bill.insurance_company_id, bill.insurance_total, " +
                " bill.total_amount,c.insurance_company_name," +
                " ROUND(bill.total_poundage_amount,2) total_poundage_amount," +
                " ROUND(bill.blance_poundage_amount,2) blance_poundage_amount," +
                " bill.blance_start_date,bill.blance_stop_date,ROUND(bill.adjust_price,2) adjustPrice," +
                " ROUND(bill.adjust_price_loss,2) adjustPriceLoss,bill.in_tax,bill.tax" +
                " from " +
                " T_INSURANCE_COMPANY_BILL bill,T_Insurance_Company c where bill.insurance_company_id=c.id ");
        sql.append(" and bill.company_id=? ");
        params.add(TokenUtil.getCompanyId(uid));
        //保险公司
        if(!"".equals(StringUtil.formatEmpty(data.get("insuranceCompany")))){
            sql.append(" and bill.insurance_company_id=? ");
            params.add(StringUtil.formatEmpty(data.get("insuranceCompany")));
        }
        //结算时间
        if(!"".equals(StringUtil.formatEmpty(data.get("blanceStartDate")))){
            sql.append(" and bill.blance_date>=? ");
            params.add(StringUtil.formatEmpty(data.get("blanceStartDate")));
        }
        //结算时间
        if(!"".equals(StringUtil.formatEmpty(data.get("blanceStopDate")))){
            sql.append(" and bill.blance_date<=? ");
            params.add(StringUtil.formatEmpty(data.get("blanceStopDate")));
        }
        //结算状态
        if(!"".equals(StringUtil.formatEmpty(data.get("blanceType")))){
            sql.append(" and bill.blance_type<=? ");
            params.add(StringUtil.formatEmpty(data.get("blanceType")));
        }
        return this.billDao.page(sql.toString(),params.toArray(),start,limit);
    }


    /**
     * 清空对账单明细
     * @param billId
     * @throws Exception
     */
    @Override
    public void moveBillDetail(String billId)throws Exception{
        this.moveBillDetailByBillId(billId);
        this.upBillBlancePougdageAmount(billId);
    }

    /**
     * 删除对账单信息，先清空对账单明细 在删除对账单
     * @param billid
     * @throws Exception
     */
    @Override
    public void delBill(String billid)throws Exception{
        this.moveBillDetailByBillId(billid);
        //清空对账单明细
        this.moveBillDetail(billid);
        String sql="delete from T_INSURANCE_COMPANY_BILL where id=?";
        this.billDao.executeSQL(sql,billid);
    }
    private void moveBillDetailByBillId(String billid) throws Exception {
        //将保单状态改为未结算
        this.billDao.executeSQL(" update t_insurance_company_bill_balance balance,t_insurance_order_item item " +
                " set item.blance_type=? " +
                " where balance.item_id=item.id and balance.bill_id=?",Constant.INSURANCE_BLANCE_TYPE.init.ordinal(),billid);
        //移除结算单里的信息
        this.billDao.executeSQL(" delete from t_insurance_company_bill_balance where bill_id=? ",billid);

        this.billDao.executeSQL(" delete from t_insurance_company_bill_detail where bill_id=? ",billid);
        this.billDao.executeSQL("delete from t_insurance_company_bill_adjust_log where bill_id=?",billid);
        this.billDao.executeSQL("update t_insurance_company_bill set adjust_price='0',adjust_price_loss='0'," +
                "blance_poundage_amount='0',insurance_total='0',total_amount='0',total_poundage_amount='0',blance_type='0' where id=?",billid);
    }


    /**
     * 结算对账单
     * 1、将符合条件的保单信息查询出来 添加进入结算单
     * 2、根据保单号、保费、被保险人、佣金与4s店保单进行对比，如完全一致 则通过，列为结清；
     * 3、更新结算单，列入保险公司保单ID、更新结算状态；同步更新4s店保单、保险公司上传保单结算状态；
     * 4、更新结算佣金
     * @param data
     * @param uid
     * @throws Exception
     */
    @Override
    public void balanceOrder(Map<String,Object> data,String uid,String billId) throws Exception {
        TInsuranceCompanyBill billBalance=this.billDao.getInfo(billId);
        //0、检测结算所针对的保单中是否有未审核通过的数据
        this.checkBIllBlanceByType(data,uid,billBalance);
        //1、将符合条件的4s店保单查询，添加进入结算单
        this.addBillBlance(data,uid,billBalance);
        //2、根据保单号、保费、被保险人、佣金与结算单的保单进行对比 如一致 则更新结算单
        this.blanceInsuaranceOrder(billBalance);
        //3、更新结算单，列入保险公司保单ID、更新结算状态；同步更新4s店保单、保险公司上传保单结算状态；
        //更新对账单
        this.updBillBlance(billId);
        //更新结算佣金
        this.upBillBlancePougdageAmount(billId);
        /**
         * 保存本次创建对账单的日志
         */
        this.saveApplayCompanyByBill(data,uid,billId);
    }

    /**
     * 保存本次对账单的日志
     * @param data
     * @param uid
     * @param billId
     * @throws Exception
     */
    private void saveApplayCompanyByBill(Map<String,Object> data,String uid,String billId) throws Exception {
        String area=StringUtil.formatEmpty(data.get("area"));
        List<String> companys=(ArrayList<String>)data.get("carCompany");
        String companyId=TokenUtil.getCompanyId(uid);
        List<TInsuranceCompanyBillLog> logs=new ArrayList<>();
        for(String company:companys){
            TInsuranceCompanyBillLog log=new TInsuranceCompanyBillLog();
            log.setApplyCompanyId(company);
            log.setArea(area);
            log.setBillId(billId);
            log.setCompanyId(companyId);
            log.setCreateTime(System.currentTimeMillis());
            log.setCreateUser(uid);
            logs.add(log);
        }
        this.billDao.saveOrUpdateList(logs);
    }

    private void checkBIllBlanceByType(Map data,String uid,TInsuranceCompanyBill billId) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        this.billBlanceToSql(sql,uid,billId,data,params);
        sql.append(" and (t.commission_type=0 or t.price_warning_type=0 )");
        List list=this.billDao.list(sql.toString(),params.toArray());
        if(list.size()>0){
            throw new Exception("所结算的数据中有保单佣金费率或保费未审核通过,请联系管理员处理!!");
        }
    }

    /**
     * 将符合条件的4s店保险单 添加进入结算单
     * @param data
     * @param uid
     * @param bill
     * @throws Exception
     */
    private void addBillBlance(Map data,String uid,TInsuranceCompanyBill bill) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append(" insert into T_INSURANCE_COMPANY_BILL_BALANCE(`id`, `create_time`, `create_user`,`bill_id`,`item_id`,`blance_type`,`insurance_type`) ");
        this.billBlanceToSql(sql,uid,bill,data,params);
        sql.append(" and t.commission_type='1' ");
        this.billDao.executeSQL(sql.toString(),params.toArray());
    }

    /**
     * 生成查询结算信息数据
     * @param sql
     * @param uid
     * @param bill
     * @param data
     * @param params
     */
    private void billBlanceToSql(StringBuffer sql,String uid,TInsuranceCompanyBill bill,Map data,List<String> params) throws Exception {
        //交强险增加移除车船税，并重新计算佣金费率，用于重新调整结算佣金
        sql.append(" SELECT  replace(t.blanceId,'-',''),'"+System.currentTimeMillis()+"','"+uid+"','"+bill.getId()+"'," +
                "  t.id,t.blance_type,t.contract_type  " +
                "  FROM  ( " +
                "  SELECT UUID() blanceId," +
                "  DATE_FORMAT(o.out_order_date, '%Y-%m-%d') out_order_date,o.commission_type," +
                "  o.price_warning_type,o.insurance_company_id," +
                "  item.id,item.create_time,item.create_user,item.dept_id,item.update_time,item.update_user,item.blance_date," +
                "  item.blance_type,item.commission,item.commission_exp,item.commission_id," +
                "  item.commission_temp,item.company_id,item.contract_no,item.contract_type,item.insurance," +
                "  item.is_commission_temp,item.order_id,item.start_date,item.stop_date,item.add_commission," +
                "  CASE WHEN item.contract_type = 'jq' THEN item.price-qitem.item_price ELSE item.price END price," +
                "  CASE WHEN item.is_commission_temp='true' THEN ROUND((item.price-qitem.item_price)*(commission_temp/100),2) ELSE " +
                "            ROUND((item.price-qitem.item_price)*(item.commission/100),2) END commission_price " +
                "  FROM t_insurance_order_item item  " +
                "  LEFT JOIN t_insurance_order o ON item.order_id = o.id  " +
                "  LEFT JOIN (SELECT * FROM t_insurance_quotation_item WHERE item_id = '0') qitem " +
                "  ON item.quotation_id = qitem.quotation_id " +
                ") t");
        sql.append(" where t.blance_type='0' and t.insurance_company_id=? ");
        List<String> carCompanyList=new ArrayList<>();
        //指定保险公司
        params.add(bill.getInsuranceCompanyId());
        if(data.get("carCompany") instanceof ArrayList){//判断是多选还是单选
            carCompanyList=(List<String>) data.get("carCompany");
        }else{
            carCompanyList.add(String.valueOf(data.get("carCompany")));
        }
        //结算规则
        String balance=StringUtil.formatEmpty(data.get("balance"));
        //开始时间
        String startDate= StringUtil.formatEmpty(data.get("startDate"));
        //结束时间
        String stopDate=StringUtil.formatEmpty(data.get("stopDate"));
        if(carCompanyList.size()>1){
            sql.append(" and t.company_id in ( ");
            for(int i=0;i<carCompanyList.size();i++){
                String[] companys=carCompanyList.get(i).split(",");
                sql.append(" ? ");
                params.add(companys[0]);
                if(i<(carCompanyList.size()-1)){
                    sql.append(",");
                }
            }
            sql.append(" ) ");
        }
        if("0".equals(balance)){//签单日期结算
            if(!"".equals(startDate)) {
                sql.append(" and t.out_order_date>=DATE_FORMAT(?,'%Y-%m-%d') ");
                params.add(startDate);
            }
            if(!"".equals(stopDate)) {
                sql.append(" and t.out_order_date<=DATE_FORMAT(?,'%Y-%m-%d') ");
                params.add(stopDate);
            }
        }else{//起保日期结算
            if(!"".equals(startDate)) {
                sql.append(" and t.start_date>=DATE_FORMAT(?,'%Y-%m-%d') ");
                params.add(startDate);
            }
            if(!"".equals(stopDate)) {
                sql.append(" and t.start_date<=DATE_FORMAT(?,'%Y-%m-%d') ");
                params.add(stopDate);
            }
        }
        sql.append(" and not EXISTS(select * from t_insurance_company_bill_balance where item_id = t.id) ");
    }


    /**
     * 更新结算信息表中的数据
     * @param bill
     * @throws Exception
     */
    private void blanceInsuaranceOrder(TInsuranceCompanyBill bill) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("UPDATE t_insurance_company_bill_balance blance,( " +
                " SELECT  blance.*, detail.id detailId," +
                " (blance.poundage-detail.poundage) as unliquidatedPoundage, " +
                " (blance.price-detail.price) as unliquidatePrice, " +
                " (blance.commission_price-detail.poundage_price) as unliquidatePoundagePirce FROM  ( " +
                " SELECT item.*, blance.id blanceId," +
                " case when item.commission_temp <>''  then item.commission_temp else item.commission end poundage  FROM " +
                " t_insurance_company_bill_balance blance, " +
                " (SELECT item.id, item.create_time, item.create_user, item.dept_id, item.update_time, item.update_user," +
                " item.blance_date, item.blance_type, item.commission, item.commission_exp, item.commission_id," +
                " item.commission_temp, item.company_id, item.contract_no, item.contract_type, item.insurance," +
                " item.is_commission_temp, item.order_id, item.start_date, item.stop_date, item.add_commission, " +
                " CASE WHEN item.contract_type = 'jq' THEN ROUND(item.price-qitem.item_price,2) ELSE item.price END price," +
                " CASE WHEN item.is_commission_temp='true' THEN ROUND((item.price-qitem.item_price)*(commission_temp/100),2) " +
                " ELSE ROUND((item.price-qitem.item_price)*(commission/100),2) END commission_price" +
                " FROM t_insurance_order_item item " +
                " LEFT JOIN (SELECT * FROM t_insurance_quotation_item WHERE item_id = '0') qitem " +
                " ON item.quotation_id = qitem.quotation_id) item " +
                " WHERE " +
                " blance.item_id = item.id  AND item.blance_type = ?  ) blance,");
        params.add(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.init.ordinal()));
        sql.append(" t_insurance_company_bill_detail detail " +
                " WHERE " +
                " blance.contract_no = detail.contract_no " +
                " AND blance.insurance = detail.insurance " +
                " AND blance.price = detail.price " +
                " AND blance.commission_price = detail.poundage_price " +
                " AND detail.bill_id=? " +
                " ) bill ");
        params.add(bill.getId());
        sql.append(" SET blance.blance_type = ?," +
                " blance.detail_id = bill.detailId, " +
                " blance.blance_date = DATE_FORMAT(NOW(), '%Y-%m-%d')," +
                " blance.unliquidated_poundage = bill.unliquidatedPoundage, " +
                " blance.unliquidated_poundage_price = round(bill.unliquidatePoundagePirce,2), "+
                " blance.unliquidated_price = round(bill.unliquidatePrice,2) "+
                " WHERE " +
                " blance.id = bill.blanceId " +
                " AND blance.blance_type = ?");
        params.add(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.blance.ordinal()));
        params.add(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.init.ordinal()));
        this.billDao.executeSQL(sql.toString(),params.toArray());
    }
    /**
     * 更新对账单信息
     * @throws Exception
     */
    public void updBillBlance(String billId) throws Exception {
        //更新对账单明细 保险公司上传的保单信息结算状态
        String detailSql="update t_insurance_company_bill_detail detail, T_INSURANCE_COMPANY_BILL_BALANCE balance  " +
                " set  " +
                " detail.blance_type=balance.blance_type,detail.blance_date=balance.blance_date " +
                " where detail.id=balance.detail_id and detail.blance_type='0' ";
        if(!"".equals(billId)){
            detailSql+="  and detail.bill_id=? ";
        }
        this.billDao.executeSQL(detailSql,billId);
        //更新4s店保险单状态
        String itemSql="update t_insurance_order_item item, T_INSURANCE_COMPANY_BILL_BALANCE balance " +
                " set " +
                " item.blance_type=balance.blance_type,item.blance_date=balance.blance_date  " +
                " where item.id=balance.item_id and item.blance_type='0' ";
        if(!"".equals(billId)){
            itemSql+=" and balance.bill_id=? ";
        }
        this.billDao.executeSQL(itemSql,billId);
        //更新保险公司对账单信息 如对账单下所有的保险单已经结算完毕，则更新对账单状态
        String sql="update  t_insurance_company_bill bill, " +
                " ( " +
                " SELECT " +
                "  COUNT(id) nums, " +
                "  bill_id " +
                " FROM " +
                "  t_insurance_company_bill_detail " +
                " WHERE " +
                "  blance_type = '1' " +
                " GROUP BY " +
                "  bill_id " +
                ")t  " +
                "set bill.blance_type='1', bill.blance_date=DATE_FORMAT(NOW(),'%Y-%m-%d') " +
                "where bill.id=t.bill_id and blance_type='0'  ";
        if(!"".equals(billId)){
            sql+=" and bill.id=? ";
        }
        sql+=" and t.nums=(select COUNT(id) from " +
                " t_insurance_company_bill_detail where bill_id=bill.id and blance_type='1')";
        this.billDao.executeSQL(sql,billId);
    }

    /**
     * 更新结算佣金
     * @throws Exception
     */
    public void upBillBlancePougdageAmount(String billId) throws Exception {
        String sql=" update  t_insurance_company_bill bill, (  " +
                "                 SELECT  COUNT(id) nums, SUM(poundage_price*1) sums, bill_id  " +
                "                 FROM  t_insurance_company_bill_detail WHERE blance_type = '1'  GROUP BY  bill_id ) t  " +
                "                 SET bill.blance_poundage_amount =ROUND(t.sums,2)  " +
                "                 WHERE  " +
                "                 bill.id = t.bill_id  ";
        if(!"".equals(billId)){
            sql+=" and bill.id=?";
        }
        this.billDao.executeSQL(sql,billId);
    }

    /**
     * 获得保险公司导入模板
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> findExcelScheme() throws Exception {
        String sql="select * from t_excel_scheme where brand=?";
        return this.billDao.list(sql,"BX");
    }


    @Override
    public ExcelSchemeVo loadExcelSchemeInfo(String schemeId) throws Exception {
        if (StringUtils.isEmpty(schemeId)) {
            throw new Exception("请选择方案");
        }

        Map schemeMap = billDao.info(TABLE_NAME, "id", schemeId);
        if (null == schemeMap || schemeMap.size() == 0) {
            throw new Exception("请选择有效的方案！");
        }

        ExcelSchemeVo esv = new ExcelSchemeVo();
        esv.setId((String) schemeMap.get("id"));
        esv.setDataEntityId((String) schemeMap.get("dataEntityId"));
        esv.setClsQualifiedName((String) schemeMap.get("qualifiedName"));
        esv.setBrand((String) schemeMap.get("brand"));
        if (StringUtils.isEmpty(esv.getClsQualifiedName())) {
            throw new Exception("请选择有效的方案！");
        }
        esv.setTitleRow(Integer.valueOf(schemeMap.get("titleRow").toString()));
        esv.setDataStartRow(Integer.valueOf(schemeMap.get("dataStartRow").toString()));
        esv.setKeyColumn(Integer.valueOf(schemeMap.get("keyColumn").toString()));

        ///设置导入标识
        esv.setUploadKey(UUID.randomUUID().toString());

        String sqlStr = "SELECT s.id, s.property_id, s.column_num, d.property_name, d.property_type "
                + " from t_excel_scheme_property  s LEFT JOIN t_data_entity_property  d on s.property_id=d.id "
                + " where s.column_num > 0 and s.scheme_id= ? ";

        List<Map<String, Object>> propMap = billDao.list(sqlStr, new Object[]{schemeId});
        if (null != propMap && propMap.size() > 0) {
            List<ExcelSchemePropertyVo> espList = new ArrayList<ExcelSchemePropertyVo>();
            ExcelSchemePropertyVo esp = null;
            for (int i = 0; i < propMap.size(); i++) {
                esp = new ExcelSchemePropertyVo();
                esp.setPropertyName(propMap.get(i).get("propertyName").toString());
                esp.setPropertyType(propMap.get(i).get("propertyType").toString());
                esp.setColumnNum(Integer.valueOf(propMap.get(i).get("columnNum").toString()));
                espList.add(esp);
            }

            esv.setProperyList(espList);
        }

        return esv;

    }

    @Autowired
    private IImpHibernateDao impHibernateDao;
    /***
     * 解析 Excel
     * @param file
     * @param scheme
     * @return
     */
    @Override
    public int[] parseExcel(MultipartFile file, ExcelSchemeVo scheme, String uid,String billId) throws Exception {
        TInsuranceCompanyBill bill=this.billDao.getInfo(billId);
        //获取公司信息
        TInsuranceCompany company=companyService.getInfo(bill.getInsuranceCompanyId());
        String jqNo=company.getContractJqNO();
        String syNo=company.getContractSyNO();
        String fileName = file.getOriginalFilename();
        String type = fileName.substring(fileName.lastIndexOf("."));
        String companyId = TokenUtil.getCompanyId(uid);
        if (type != null && (type.endsWith("xlsx") || type.endsWith("xls"))) {
            //2007之后版本
            int successNum[] = {0, 0};
            try {
                ExcelWorkSheetRowCallbackHandler sheetRowCallbackHandler =
                        new ExcelWorkSheetRowCallbackHandler(new ExcelRowContentCallback() {
                            @Override
                            public void processRow(int rowNum, Map<String, String> map) throws Exception {
                                if (scheme.getDataStartRow() - 1 <= rowNum && null != map) {

                                    BaseEntity be = EntityFactory.BuildEntity(scheme, new ArrayList(map.values()), uid, companyId,null);
                                    if (null != be) {
                                        successNum[0]++;

                                        TInsuranceCompanyBillDetail detail=new TInsuranceCompanyBillDetail();
                                        BeanUtils.copyProperties(be,detail);
                                        if(!StringUtil.formatEmpty(detail.getContractNo()).equals("")) {
                                            String contractType=StringUtil.formatEmpty(detail.getContractType());
                                            if (detail.getContractNo().indexOf(jqNo) != -1 || contractType.indexOf("交强") != -1) {
                                                detail.setContractType("jq");
                                            }
                                            if (detail.getContractNo().indexOf(syNo) != -1 || contractType.indexOf("商业") != -1) {
                                                detail.setContractType("sy");
                                            }
                                            detail.setBillId(billId);
                                            if (detail.getPoundage().indexOf("%") != -1) {
                                                detail.setPoundage(detail.getPoundage().replaceAll("%", ""));
                                            }
                                            Float poundage = Float.valueOf(detail.getPoundage());
                                            if (poundage < 1 && poundage > 0) {
                                                detail.setPoundage(String.valueOf(poundage * 100));
                                            }
                                            Float price = Float.valueOf(detail.getPoundagePrice());
                                            detail.setInsuranceCompanyId(bill.getInsuranceCompanyId());
                                            detail.setBlanceType("0");
                                            billDao.saveOrUpdate(detail);
                                        }
                                    }
                                }
                            }
                        });
                sheetRowCallbackHandler.setHeadRow(scheme.getTitleRow() - 1);
                PoiExcelUtil.readExcel(type, file.getInputStream(), 0, sheetRowCallbackHandler, null);
                this.updCommissionPrice(billId);
                //2、根据保单号、保费、被保险人、佣金与结算单的保单进行对比 如一致 则更新结算单
                this.blanceInsuaranceOrder(bill);
                //3、更新结算单，列入保险公司保单ID、更新结算状态；同步更新4s店保单、保险公司上传保单结算状态；
                //更新对账单
                this.updBillBlance(billId);
                //更新结算佣金
                this.upBillBlancePougdageAmount(billId);
            } catch (RuntimeException are) {
                are.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
            return successNum;

        } else {
            throw new Exception("无法识别的文件格式！");
        }
    }

    /**
     * 更新总佣金
     * @param billId
     * @throws Exception
     */
    private void updCommissionPrice(String billId) throws Exception {
        String sql=" UPDATE t_insurance_company_bill bill, ( " +
                " SELECT " +
                " SUM(poundage_price * 1) poungaetPrice, " +
                " SUM(price * 1) price, " +
                " COUNT(id) totals, " +
                " bill_id " +
                " FROM " +
                " t_insurance_company_bill_detail " +
                " GROUP BY bill_id  ) detail " +
                " SET bill.total_poundage_amount = ROUND(detail.poungaetPrice, 2), " +
                " bill.total_amount = ROUND(detail.price, 2), " +
                " bill.insurance_total=detail.totals " +
                " WHERE " +
                " bill.id = detail.bill_id " +
                " AND bill.id =? ";
        this.billDao.executeSQL(sql,billId);
    }

    /**
     * 查询结算明细
     * @param billId
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map getBillDetailList(String billId,String start,String limit,Map<String,Object> data) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        this.queryBillDetailToSql(sql,params,data,billId);
        Map map=this.billDao.page(sql.toString(),params.toArray(),start,limit);
        map.put("sy",this.syStatisticsBillDetails(billId,data));
        map.put("jq",this.jqStatisticsBillDetails(billId,data));
        map.put("all",this.allStatisticsBillDetails(billId,data));
        return map;
    }

    /**
     * 汇总单交强险信息
     * @param billId
     * @param data
     * @return
     * @throws Exception
     */
    private Map jqStatisticsBillDetails(String billId,Map<String,Object> data) throws Exception {
        //交强险信息
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("SELECT COUNT(t.jq_id) total,round(SUM(t.jq_price),2) price,round(SUM(t.jq_poundage_price),2) poundagePrice FROM (");
        this.queryBillDetailToSql(sql,params,data,billId);
        sql.append(" )t where t.sy_id is null");
        return this.billDao.info(sql.toString(),params.toArray());
    }

    /**
     * 汇总单商业险信息
     * @param billId
     * @param data
     * @return
     * @throws Exception
     */
    private Map syStatisticsBillDetails(String billId,Map<String,Object> data) throws Exception {
        //商业险信息
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("SELECT COUNT(t.sy_id) total,round(SUM(t.sy_price),2) price,round(SUM(t.sy_poundage_price),2) poundagePrice FROM (");
        this.queryBillDetailToSql(sql,params,data,billId);
        sql.append(" )t where t.jq_id is null");
        return this.billDao.info(sql.toString(),params.toArray());
    }

    /**
     * 汇总对账单所有信息
     * @param billId
     * @param data
     * @return
     * @throws Exception
     */
    private Map allStatisticsBillDetails(String billId,Map<String,Object> data) throws Exception {
        //所有险信息
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append("SELECT COUNT(1) total,round(SUM(t.jq_price),2) jq_price,round(SUM(t.jq_poundage_price),2) jq_poundagePrice, " +
                "round(SUM(t.sy_price),2) sy_price,round(SUM(t.sy_poundage_price),2) sy_poundagePrice FROM (");
        this.queryBillDetailToSql(sql,params,data,billId);
        sql.append(" )t ");
        return this.billDao.info(sql.toString(),params.toArray());
    }

    /**
     * 生成查询sal
     * @param sql
     * @param params
     * @param data
     * @param billId
     * @throws Exception
     */
    private void queryBillDetailToSql(StringBuffer sql,List<String> params,Map<String,Object> data,String billId) throws Exception {
        if(data==null){
            throw new Exception("请至少选择一项对应条件!!");
        }
        if(data.size()==0){
            throw new Exception("请至少选择一项对应条件!!");
        }
        sql.append("SELECT  *  FROM " +
                " (SELECT ");
        //加载列显示字段
        this.queryField(sql);
        sql.append(" FROM " +
                " (SELECT * FROM  t_insurance_company_bill_detail WHERE bill_id = ? " +
                " AND contract_type = 'sy') sy " +
                " LEFT JOIN ( " +
                " SELECT  * FROM  t_insurance_company_bill_detail WHERE bill_id = ? " +
                " AND contract_type = 'jq') jq " +
                " ON ");
        params.add(billId);
        params.add(billId);
        this.queryParamToSql(sql,data);
        sql.append(" UNION " +
                " SELECT ");
        //加载列显示字段
        this.queryField(sql);
        sql.append(" FROM " +
                " (SELECT * FROM t_insurance_company_bill_detail WHERE bill_id = ? " +
                " AND contract_type = 'sy') sy " +
                " RIGHT JOIN ( " +
                " SELECT * FROM t_insurance_company_bill_detail WHERE bill_id = ? " +
                " AND contract_type = 'jq'  ) jq " +
                " ON ");
        params.add(billId);
        params.add(billId);
        this.queryParamToSql(sql,data);
        sql.append(" ) t");
    }
    //列表查询字段
    private void queryField(StringBuffer sql){
        sql.append(" sy.id sy_id, sy.bill_id sy_bill_id, sy.blance_type sy_blance_type, sy.company_id sy_company_id, " +
                " sy.contract_no sy_contract_no, sy.contract_type sy_contract_type, sy.insurance sy_insurance," +
                " sy.insurance_company_id sy_insurance_company_id, sy.insurance_type sy_insurance_type, " +
                " FROM_UNIXTIME(sy.out_order_date/1000,'%Y-%m-%d') sy_out_order_date, sy.plate_num sy_plate_num, sy.poundage sy_poundage, " +
                " sy.poundage_price sy_poundage_price, sy.price sy_price," +
                " FROM_UNIXTIME(sy.start_date/1000,'%Y-%m-%d') sy_start_date,FROM_UNIXTIME( sy.stop_date/1000,'%Y-%m-%d') sy_stop_date," +
                " sy.vin sy_vin, sy.blance_date sy_blance_date, " +
                " jq.id jq_id, jq.bill_id jq_bill_id, jq.blance_type jq_blance_type, jq.company_id jq_company_id, " +
                " jq.contract_no jq_contract_no, jq.contract_type jq_contract_type, jq.insurance jq_insurance, " +
                " jq.insurance_company_id jq_insurance_company_id, jq.insurance_type jq_insurance_type, " +
                " FROM_UNIXTIME(jq.out_order_date/1000,'%Y-%m-%d') jq_out_order_date, " +
                " jq.plate_num jq_plate_num, jq.poundage jq_poundage, jq.poundage_price jq_poundage_price, jq.price jq_price, " +
                " FROM_UNIXTIME(jq.start_date/1000,'%Y-%m-%d') jq_start_date, " +
                " FROM_UNIXTIME(jq.stop_date/1000,'%Y-%m-%d') jq_stop_date, jq.vin jq_vin, jq.blance_date jq_blance_date ");
    }
    //根据查询条件 生成对应字段
    private void queryParamToSql(StringBuffer sql,Map<String,Object> data) throws Exception {
        List<String> list=new ArrayList<>();
        list.addAll(data.keySet());
        int params=0;
        for(int i=0;i<list.size();i++){
            String key=list.get(i);
            if(params>0){
                if("true".equals(StringUtil.formatEmpty(data.get(key)))){
                    params++;
                    sql.append(" AND ");
                    sql.append(" sy."+key+" = jq."+key+" ");
                }
            }else{
                if("true".equals(StringUtil.formatEmpty(data.get(key)))){
                    params++;
                    sql.append(" sy."+key+" = jq."+key+" ");
                }
            }
        }
        if(params==0){
            throw new Exception("请至于选择一项进行匹配查询!!");
        }
    }


    @Override
    public XSSFWorkbook billDetailExpExcel(String uid,String billId,Map data) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        this.queryBillDetailToSql(sql,params,data,billId);
        List<Map<String,Object>> list=this.billDao.list(sql.toString(),params.toArray());
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("对账单--明细报表");
        String[] excelHeaderRecordout = {"商业险保单号", "商业险被保险人", "商业险保险金额", "商业险佣金率","商业险佣金","商业险开始时间","商业险结束时间","商业险出单日期",
                                         "交强险保单号", "交强险被保险人", "交强险保险金额", "交强险佣金率","交强险佣金","交强险开始时间","交强险结束时间","交强险出单日期"};
        XSSFRow row = sheet.createRow((int) 0);


        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            sheet.autoSizeColumn(i);
        }
        for (int i = 0; i < list.size(); i++) {
            Map<String,Object> map=list.get(i);
            row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(StringUtil.formatEmpty(map.get("syContractNo")));
            row.createCell(1).setCellValue(StringUtil.formatEmpty(map.get("syInsurance")));
            row.createCell(2).setCellValue(StringUtil.formatEmpty(map.get("syPrice")));
            row.createCell(3).setCellValue(StringUtil.formatEmpty(map.get("syPoundage")));
            row.createCell(4).setCellValue(StringUtil.formatEmpty(map.get("syPoundagePrice")));
            row.createCell(5).setCellValue(StringUtil.formatEmpty(map.get("syStartDate")));
            row.createCell(6).setCellValue(StringUtil.formatEmpty(map.get("syStopDate")));
            row.createCell(7).setCellValue(StringUtil.formatEmpty(map.get("syOutOrderDate")));

            row.createCell(8).setCellValue(StringUtil.formatEmpty(map.get("jqContractNo")));
            row.createCell(9).setCellValue(StringUtil.formatEmpty(map.get("jqInsurance")));
            row.createCell(10).setCellValue(StringUtil.formatEmpty(map.get("jqPrice")));
            row.createCell(11).setCellValue(StringUtil.formatEmpty(map.get("jqPoundage")));
            row.createCell(12).setCellValue(StringUtil.formatEmpty(map.get("jqPoundagePrice")));
            row.createCell(13).setCellValue(StringUtil.formatEmpty(map.get("jqStartDate")));
            row.createCell(14).setCellValue(StringUtil.formatEmpty(map.get("jqStopDate")));
            row.createCell(15).setCellValue(StringUtil.formatEmpty(map.get("jqOutOrderDate")));
        }
        return wb;
    }

    
    /**
     * 对账单明细查询，根据对账单信息
     * @param uid
     * @param billId
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public Map billDetailPage(String uid, String billId, Map data,String start,String limit) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append(" SELECT detail.*,balance.id balanceId,concat(balance.unliquidated_poundage,'%') unliquidated_poundage," +
                " concat('￥',balance.unliquidated_poundage_price) unliquidated_poundage_price," +
                " concat('￥',balance.unliquidated_price) unliquidated_price," +
                " balance.adjust_exp,balance.adjust_reason " +
                " FROM" +
                " t_insurance_company_bill_detail detail" +
                " left JOIN" +
                " t_insurance_company_bill_balance balance" +
                " ON " +
                " detail.id=balance.detail_id " +
                " where detail.bill_id=?");
        params.add(billId);
        if(!"".equals(StringUtil.formatEmpty(data.get("startDate")))){
            sql.append(" and balance.blance_date>=? ");
            params.add(StringUtil.formatEmpty(data.get("startDate")));
        }
        if(!"".equals(StringUtil.formatEmpty(data.get("stopDate")))){
            sql.append(" and balance.blance_date<=? ");
            params.add(StringUtil.formatEmpty(data.get("stopDate")));
        }
        //保单类别
        if(!"".equals(StringUtil.formatEmpty(data.get("contractType")))){
            sql.append(" and detail.contract_type=? ");
            params.add(StringUtil.formatEmpty(data.get("contractType")));
        }
        //结算状态
        if(!"".equals(StringUtil.formatEmpty(data.get("blanceType")))){
            sql.append(" and balance.blance_type=? ");
            params.add(StringUtil.formatEmpty(data.get("blanceType")));
        }
        return this.billDao.page(sql.toString(),params.toArray(),start,limit);
    }

    /**
     * 移除对账单中的保单信息
     * @param uid
     * @param data
     * @throws Exception
     */
    @Override
    public void removeDetailById(String uid,List<Map> data) throws Exception {
        if(data.size()==0){
            throw new Exception("请选择要移除的保单信息！！");
        }
        //移除保险公司上传保单信息
        StringBuffer sql=new StringBuffer("update t_insurance_company_bill_detail detail,(select * from t_insurance_company_bill_balance where item_id in(");
        //更新4s店保单状态
        StringBuffer sqlItem=new StringBuffer("update t_insurance_order_item item,(select * from t_insurance_company_bill_balance where item_id in(");
        //删除结算单信息
        StringBuffer sqlBlance=new StringBuffer("delete from t_insurance_company_bill_balance  where item_id in (" );
        List<String> params=new ArrayList<>();
        StringBuffer sqllite=new StringBuffer();
        for(int i=0;i<data.size();i++){
            sqllite.append("?");
            params.add(StringUtil.formatEmpty(data.get(i).get("id")));
            if(i<(data.size()-1)){
                sqllite.append(",");
            }
        }
        sql.append(sqllite).append(" ) )balance  set detail.blance_type='0',detail.blance_date = NULL where detail.id=balance.detail_id");
        sqlBlance.append(sqllite.toString()).append(" ) ");
        sqlItem.append(sqllite.toString()).append(" )) balance set item.blance_type='0',item.blance_date = NULL where item.id=balance.item_id");
        this.billDao.executeSQL(sqlItem.toString(),params.toArray());
        this.billDao.executeSQL(sqlBlance.toString(),params.toArray());
        this.billDao.executeSQL(sql.toString(),params.toArray());
        //移除之后更新结算状态
        updBillBlance("");
        //更新结算佣金
        upBillBlancePougdageAmount("");
    }

    /**
     * 获取对账单详情
     * @param id
     * @return
     * @throws Exception
     */
    @Override
    public TInsuranceCompanyBill getBillInfo(String id) throws Exception {
        return this.billDao.getInfo(id);
    }


    /**
     * 差异明细查询展示
     * @param uid
     * @param billId
     * @param start
     * @param limit
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public Map diffDetailPage(String uid,String billId,String start,String limit,Map data) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        this.diffDetailQueryToSql(sql,params,billId,uid,data);
        return this.billDao.page(sql.toString(),params.toArray(),start,limit);
    }
    
    private void diffItemPriceSql(StringBuffer sql,List<String> params){
        sql.append("(" +
                " SELECT item.id,item.create_time,item.create_user,item.dept_id,item.update_time,item.update_user," +
                " item.blance_date,item.blance_type,item.commission,item.commission_exp,item.commission_id," +
                " item.commission_temp,item.company_id,item.contract_no,item.contract_type,item.insurance," +
                " item.is_commission_temp,item.order_id,item.start_date,item.stop_date,item.add_commission," +
                " CASE WHEN item.contract_type = 'jq' THEN ROUND(item.price-qitem.item_price,2) ELSE item.price END price," +
                " CASE " +
                " WHEN item.is_commission_temp = 'true' and item.contract_type='jq' THEN" +
                " ROUND((item.price - qitem.item_price) * (commission_temp / 100),2)" +
                " WHEN item.is_commission_temp = 'false' and item.contract_type='jq' THEN" +
                " ROUND((item.price - qitem.item_price) * (commission / 100),2)" +
                " WHEN item.is_commission_temp = 'false' and item.contract_type='sy' THEN" +
                " ROUND(item.price * (commission / 100),2)" +
                " WHEN item.is_commission_temp = 'true' and item.contract_type='sy' THEN" +
                " ROUND(item.price * (commission_temp / 100),2)" +
                " END commission_price" +
                " FROM" +
                " t_insurance_order_item item" +
                " LEFT JOIN (SELECT * FROM t_insurance_quotation_item WHERE item_id = '0') qitem " +
                " ON item.quotation_id = qitem.quotation_id)item");
    }

    private void diffDetailQueryToSql(StringBuffer sql,List<String> params,String billId,String uid,Map data) throws Exception {
        //获得对账单明细，判断其是包含 还是不包含
        TInsuranceCompanyBill bill=this.billDao.getInfo(billId);
        Float tax=1f;
        if(String.valueOf(Constant.BILL_IN_TAX.ex.ordinal()).equals(StringUtil.formatEmpty(bill.getInTax()))){
            tax=Float.valueOf(bill.getTax());
        }
        String companyId="";
        if(data.get("companyId")!=null){
            if(!"".equals(StringUtil.formatEmpty(data.get("companyId")))){
                companyId=StringUtil.formatNull(data.get("companyId"));
            }
            data.remove("companyId");
        }
        String balanceType="";
        if(!"".equals(StringUtil.formatEmpty(data.get("balanceType")))){
            if("true".equals(StringUtil.formatEmpty(data.get("balanceType")))) {
                balanceType = StringUtil.formatNull(data.get("balanceType"));
            }
            data.remove("balanceType");
        }
        sql.append(" select * from (");
        //判断是否选择了结算
        if(balanceType.equals("true")){
            sql.append(" SELECT" );
            this.getQueryFieldToSql(sql,tax);
            sql.append(" FROM (" +
                    " SELECT item.*, " +
                    " balance.id balanceId,balance.adjust_reason,balance.adjust_exp,balance.unliquidated_poundage_price," +
                    " balance.unliquidated_price,balance.detail_id,balance.unliquidated_poundage,item.commission_price poundage_price " +
                    " FROM " +
                    " t_insurance_company_bill_balance balance,");
//                    " t_insurance_order_item item " +
            diffItemPriceSql(sql,params);
            sql.append(" WHERE " +
                    " balance.item_id = item.id AND balance.bill_id =? AND balance.blance_type = '1'");
            params.add(billId);
            this.getQueryCompanyToSql(sql,params,companyId,uid,"item");
            sql.append(" ) t," +
                    " t_insurance_company_bill_detail detail" +
                    " WHERE" +
                    " detail.bill_id =? AND detail.blance_type = '1'" +
                    " AND t.contract_type = detail.contract_type " +
                    " AND t.detail_id=detail.id ");
            params.add(billId);
            sql.append(" UNION ");
        }
        //保险公司有、4s店有
        sql.append(" SELECT" );
        this.getQueryFieldToSql(sql,tax);
        sql.append(" FROM (" +
                " SELECT item.*, " +
                " balance.id balanceId,balance.adjust_reason,balance.adjust_exp,balance.unliquidated_poundage_price," +
                " balance.unliquidated_price,balance.unliquidated_poundage,item.commission_price poundage_price " +
                " FROM " +
                " t_insurance_company_bill_balance balance,");
//                    " t_insurance_order_item item " +
        diffItemPriceSql(sql,params);
        sql.append(" WHERE " +
                " balance.item_id = item.id AND balance.bill_id =? AND balance.blance_type = '0'");
        params.add(billId);
        this.getQueryCompanyToSql(sql,params,companyId,uid,"item");
        sql.append(" ) t," +
                " t_insurance_company_bill_detail detail" +
                " WHERE" +
                " detail.bill_id =? AND detail.blance_type = '0'" +
                " AND t.contract_type = detail.contract_type");
        params.add(billId);
        this.getQueryParamsToSql(sql,data);
        sql.append(
                " UNION " +
                " SELECT");
        //保险公司有、4s店没有
        this.getQueryFieldToSql(sql,tax);
        sql.append(" from t_insurance_company_bill_detail detail left join " +
                " (SELECT item.*, " +
                " balance.id balanceId,balance.adjust_reason,balance.adjust_exp,balance.unliquidated_poundage_price," +
                " balance.unliquidated_price,balance.unliquidated_poundage,item.commission_price poundage_price  " +
                " FROM " +
                " t_insurance_company_bill_balance balance,");
//                    " t_insurance_order_item item " +
        diffItemPriceSql(sql,params);
        sql.append(" WHERE " +
                " balance.item_id = item.id" +
                " AND balance.bill_id =? and balance.blance_type='0' ");
        params.add(billId);
        this.getQueryCompanyToSql(sql,params,companyId,uid,"item");
        sql.append(" ) t" +
                " ON " +
                " t.contract_type = detail.contract_type");
        this.getQueryParamsToSql(sql,data);
        sql.append(
                " where  detail.bill_id =? and detail.blance_type='0'");
        params.add(billId);
        sql.append(
                " UNION " +
                " SELECT");
        //保险公司没有、4s店有
        this.getQueryFieldToSql(sql,tax);
        sql.append(" from t_insurance_company_bill_detail detail RIGHT join " +
                " (SELECT item.*, " +
                " balance.id balanceId,balance.adjust_reason,balance.adjust_exp,balance.unliquidated_poundage_price," +
                " balance.unliquidated_price,balance.unliquidated_poundage,item.commission_price poundage_price " +
                " FROM " +
                " t_insurance_company_bill_balance balance,");
//                    " t_insurance_order_item item " +
        diffItemPriceSql(sql,params);
        sql.append(" WHERE " +
                " balance.item_id = item.id");
        this.getQueryCompanyToSql(sql,params,companyId,uid,"item");
        sql.append(
                " AND balance.bill_id =? and balance.blance_type='0' ) t" +
                " ON " +
                " t.contract_type = detail.contract_type ");
        params.add(billId);

        this.getQueryParamsToSql(sql,data);
        sql.append(") temp order by temp.create_time desc");
    }

    /**
     * 字段列表
     * @param sql
     */
    private void getQueryFieldToSql(StringBuffer sql,Float tax){
        sql.append(" detail.id AS detailId,detail.contract_no detailContractNo,detail.insurance detailInsurance," +
                " detail.price detailPrice," +
                " detail.poundage detailPoundage,detail.poundage_price detailPoundagePrice,detail.contract_type AS detailContractType," +
                " t.id AS itemId,t.contract_no itemContractNo,t.insurance itemInsurance,t.price itemPrice," +
                " CASE t.is_commission_temp WHEN 'true' THEN t.commission_temp ELSE commission END itemPoundage," +
                " t.commission_price itemPoundagePrice," +
                " t.contract_type AS itemContractType," +
                " t.balanceId bananceId,t.create_time,t.adjust_reason,t.adjust_exp," +
                " t.unliquidated_poundage_price,t.unliquidated_price,t.unliquidated_poundage,t.blance_type ");
    }

    /**
     * 匹配参数
     * @param sql
     * @param data
     */
    private void getQueryParamsToSql(StringBuffer sql,Map data){
        List<String> list=new ArrayList<>();
        list.addAll(data.keySet());
        int params=0;
        for(int i=0;i<list.size();i++) {
            String key = list.get(i);
            if ("true".equals(StringUtil.formatEmpty(data.get(key)))) {
                params++;
                sql.append(" AND t." + key + " = detail." + key + " ");
            }
        }
    }

    private void getQueryCompanyToSql(StringBuffer sql,List<String> params,String companyId,String uid,String byName){
        if("".equals(companyId)){
            Constant.setQueryByRoleDate(uid,sql,params,byName);
        }else{
            sql.append(" AND "+byName+".company_id = ?");
            params.add(companyId);
        }
    }

    /**
     * 差异明细生成查询sql
     * @param sql
     * @param data
     * @param params
     * @throws Exception
     */
    private void diffQueryToSql(StringBuffer sql,Map data,List<String> params,String billId,String uid) throws Exception {
        sql.append("SELECT " +
                " detail.id AS detailId, " +
                " detail.contract_no detailContractNo, " +
                " detail.insurance detailInsurance, " +
                " detail.price detailPrice, " +
                " detail.poundage detailPoundage, " +
                " detail.poundage_price detailPoundagePrice, " +
                " detail.contract_type AS detailContractType, " +
                " t.id AS itemId, " +
                " t.contract_no itemContractNo, " +
                " t.insurance itemInsurance, " +
                " t.price itemPrice, " +
                " CASE t.is_commission_temp when 'true' then t.commission_temp else commission end itemPoundage, " +
                " t.commission_price itemPoundagePrice, " +
                " t.contract_type AS itemContractType, " +
                " t.balanceId bananceId " +
                "FROM " +
                " ( " +
                "  SELECT " +
                "   item.*, balance.id balanceId " +
                "  FROM " +
                "   t_insurance_company_bill_balance balance, " +
                "   t_insurance_order_item item " +
                "  WHERE " +
                "   balance.item_id = item.id and balance.bill_id=? and balance.blance_type='0' " +
                " ) t, " +
                " t_insurance_company_bill_detail detail " +
                "WHERE detail.bill_id=? AND detail.blance_type='0' " +
                "AND t.contract_type = detail.contract_type ");
        params.add(billId);
        params.add(billId);
        int i=0;
        //是否勾选佣金
        if(StringUtil.formatEmpty(data.get("poundage_price")).equals("true")){
            sql.append(" AND t.commission_price = detail.poundage_price ");
            i++;
        }
        //是否勾选保险单号
        if(StringUtil.formatEmpty(data.get("contract_no")).equals("true")){
            sql.append(" AND t.contract_no = detail.contract_no ");
            i++;
        }
        //是否勾选保险金额
        if(StringUtil.formatEmpty(data.get("price")).equals("true")){
            sql.append(" AND t.price = detail.price ");
            i++;
        }
        //是否勾选被保险人
        if(StringUtil.formatEmpty(data.get("insurance")).equals("true")){
            sql.append(" AND t.insurance = detail.insurance ");
            i++;
        }
        if(!"".equals(StringUtil.formatEmpty(data.get("companyId")))){
            sql.append(" AND t.company_id=? ");
            params.add(TokenUtil.getCompanyId(uid));
        }
        if(i<=1){
            throw new Exception("请至少选择两项结算选择！！");
        }
    }

    @Override
    public void diffBalanceDetails(String uid,String billId,Map data) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        //取出查询条件
        Map queryParams= (Map) data.get("data");
        //取出所选数据
        List<Map> blanceList= (List<Map>) data.get("blanceList");
        //分别取出明细保险公司保单明细ID 与 结算单ID
        List<String> detailIds=new ArrayList<>();
        List<String> blanceids=new ArrayList<>();
        for(Map blance:blanceList){
            String detailId=StringUtil.formatEmpty(blance.get("detailId"));
            String blanceId=StringUtil.formatEmpty(blance.get("blanceId"));
            detailIds.add(detailId);
            blanceids.add(blanceId);
        }
        this.deffBalanceDetailToSql(sql,params,queryParams,billId,detailIds,blanceids);
        this.billDao.executeSQL(sql.toString(),params.toArray());
        //3、更新结算单，列入保险公司保单ID、更新结算状态；同步更新4s店保单、保险公司上传保单结算状态；
        //更新对账单
        this.updBillBlance(billId);
        //更新结算佣金
        this.upBillBlancePougdageAmount(billId);
        this.diffBalanceByAdjustPrice(billId);
    }

    /**
     * 差异明细 自动结算
     * @param uid
     * @param billId
     * @param data
     * @throws Exception
     */
    @Override
    public void diffBalanceDetail(String uid,String billId,Map data) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        this.deffBalanceDetailToSql(sql,params,data,billId,null,null);
        this.billDao.executeSQL(sql.toString(),params.toArray());
        //3、更新结算单，列入保险公司保单ID、更新结算状态；同步更新4s店保单、保险公司上传保单结算状态；
        //更新对账单
        this.updBillBlance(billId);
        //更新结算佣金
        this.upBillBlancePougdageAmount(billId);
        //更新调整金额
        this.diffBalanceByAdjustPrice(billId);
    }

    private void deffBalanceDetailToSql(StringBuffer sql,List<String> params,Map data,String billId,List<String> detailIds,List<String> blanceIds) throws Exception {
        sql.append("UPDATE t_insurance_company_bill_balance blance,( " +
                " SELECT  blance.*, detail.id detailId," +
                " (detail.poundage-blance.poundage) as unliquidatedPoundage, " +
                " (detail.price-blance.price) as unliquidatePrice, " +
                " (detail.poundage_price-blance.commission_price) as unliquidatePoundagePirce FROM  ( " +
                " SELECT item.*, blance.id blanceId, " +
                " case when item.commission_temp <>''  then item.commission_temp else item.commission end poundage  FROM " +
                " t_insurance_company_bill_balance blance, ");

//                " t_insurance_order_item item " +
         diffItemPriceSql(sql,params);
         sql.append(" WHERE " +
                " blance.item_id = item.id  AND item.blance_type = ?  ) blance,");
        params.add(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.init.ordinal()));
        sql.append(" t_insurance_company_bill_detail detail " +
                " WHERE detail.bill_id=?");
        if(detailIds!=null){
            sql.append(" and detail.id in( ");
            for(int i=0;i<detailIds.size();i++){
                sql.append(" ? ");
                params.add(detailIds.get(i));
                if(i<(detailIds.size()-1)){
                    sql.append(" , ");
                }
            }
            sql.append(" ) ");
        }
        if(blanceIds!=null){
            sql.append(" and blance.id in( ");
            for(int i=0;i<blanceIds.size();i++){
                sql.append(" ? ");
                params.add(blanceIds.get(i));
                if(i<(blanceIds.size()-1)){
                    sql.append(" , ");
                }
            }
            sql.append(" ) ");
        }
        int i=0;
        if(StringUtil.formatEmpty(data.get("poundage_price")).equals("true")){
            sql.append(" AND blance.commission_price = detail.poundage_price ");
            i++;
        }
        //是否勾选保险单号
        if(StringUtil.formatEmpty(data.get("contract_no")).equals("true")){
            sql.append(" AND blance.contract_no = detail.contract_no ");
            i++;
        }
        //是否勾选保险金额
        if(StringUtil.formatEmpty(data.get("price")).equals("true")){
            sql.append(" AND blance.price = detail.price ");
            i++;
        }
        //是否勾选被保险人
        if(StringUtil.formatEmpty(data.get("insurance")).equals("true")){
            sql.append(" AND blance.insurance = detail.insurance ");
            i++;
        }
        if(i<=1){
            throw new Exception("请至少选择两项结算选择！！");
        }
        sql.append(" ) bill ");
        params.add(billId);
        sql.append(" SET blance.blance_type = ?,blance.detail_id = bill.detailId, " +
                " blance.blance_date = DATE_FORMAT(NOW(), '%Y-%m-%d'), " +
                " blance.unliquidated_poundage = bill.unliquidatedPoundage, " +
                " blance.unliquidated_poundage_price = round(bill.unliquidatePoundagePirce,2) ,"+
                " blance.unliquidated_price = round(bill.unliquidatePrice,2) "+
                " WHERE " +
                " blance.id = bill.blanceId " +
                " AND blance.blance_type = ?");
        params.add(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.blance.ordinal()));
        params.add(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.init.ordinal()));
    }

    /**
     * 结算 并 更新调整因素 调整说明
     * @param uid
     * @param billId
     * @param detailId
     * @param data
     * @param blanceId
     * @throws Exception
     */
    @Override
    @Deprecated
    public void diffBalanceDetailAndUpd(String uid, String billId, String detailId, Map data, String blanceId) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        List<String> detailIds=new ArrayList<>();
        detailIds.add(detailId);
        List<String> blanceIds=new ArrayList<>();
        blanceIds.add(blanceId);
        this.deffBalanceDetailToSql(sql,params,data,billId,detailIds,blanceIds);
        this.billDao.executeSQL(sql.toString(),params.toArray());

    }

    private enum ADJUST{
        YJ,YJL
    }

    @Override
    public void diffBalanceDetailAdjust(String uid, String billId,Map data) throws Exception {
        //取出调整因素
        String adjustReason=StringUtil.formatEmpty(data.get("adjustReason"));
        if("".equals(adjustReason)){
            throw new Exception("请选择要调整的因素信息!!");
        }
        TInsuranceCompanyBillAdjustLog adjustLog=new TInsuranceCompanyBillAdjustLog();
        adjustLog.setAdjustReason(adjustReason);
        adjustLog.setBillId(billId);
        //判定是佣金 还是佣金率
        if(adjustReason.toLowerCase().equals(ADJUST.YJ.name().toLowerCase())){
            //调整佣金 保单号、被保险人 要一致
            this.diffBalanceDetailAdjustByCommission(data,billId,adjustLog);
            adjustLog.setAdjustReason("保费佣金");
        }else{
            //调整佣金率 保单号、被保险人、保费要一致
            this.diffBalanceDetailAdjustByCommissionRate(data,billId,adjustLog);
            adjustLog.setAdjustReason("保费佣金费率");
        }
        this.updCommissionPrice(billId);
        //更新对账单
        this.updBillBlance(billId);
        //更新结算佣金
        this.upBillBlancePougdageAmount(billId);
        //更新调整金额
        this.diffBalanceByAdjustPrice(billId);
        adjustLog.setCreateUser(uid);
        adjustLog.setCreateTime(System.currentTimeMillis());
        this.billDao.saveOrUpdate(adjustLog);
    }

    /**
     * 更新调整金额
     * @param billId
     * @throws Exception
     */
    private void diffBalanceByAdjustPrice(String billId) throws Exception {
        StringBuffer sql=new StringBuffer();
        sql.append(" update t_insurance_company_bill bill, " +
                " (select ROUND(SUM(temp.unliquidated_poundage_price*1),2) unliquidated_poundage_price , " +
                " temp.bill_id  from (" +
                " SELECT id,create_time,create_user,dept_id,update_time,update_user,adjust_exp,adjust_reason,bill_id," +
                " blance_date,blance_type,detail_id,insurance_type,item_id," +
                " CASE WHEN unliquidated_poundage is null then '0' else unliquidated_poundage END unliquidated_poundage," +
                " CASE WHEN unliquidated_poundage_price is null then '0' else unliquidated_poundage_price END unliquidated_poundage_price," +
                " CASE WHEN unliquidated_poundage_price_temp is NULL then '0' else unliquidated_poundage_price_temp END unliquidated_poundage_price_temp," +
                " CASE WHEN unliquidated_price =null then '0' else unliquidated_price END unliquidated_price" +
                " FROM" +
                " t_insurance_company_bill_balance" +
                " ) temp where  temp.bill_id=? and temp.unliquidated_poundage_price>0 group by temp.bill_id) " +
                " balance " +
                " set bill.adjust_price=balance.unliquidated_poundage_price " +
                " where bill.id=balance.bill_id and bill.id=? ");
        this.billDao.executeSQL(sql.toString(),billId,billId);


        StringBuffer sqlLoss=new StringBuffer();
        sqlLoss.append(" update t_insurance_company_bill bill, " +
                " (select ROUND(SUM(temp.unliquidated_poundage_price*1),2) unliquidated_poundage_price , " +
                " temp.bill_id  from (" +
                " SELECT id,create_time,create_user,dept_id,update_time,update_user,adjust_exp,adjust_reason,bill_id," +
                " blance_date,blance_type,detail_id,insurance_type,item_id," +
                " CASE WHEN unliquidated_poundage is null then '0' else unliquidated_poundage END unliquidated_poundage," +
                " CASE WHEN unliquidated_poundage_price is null then '0' else unliquidated_poundage_price END unliquidated_poundage_price," +
                " CASE WHEN unliquidated_poundage_price_temp is NULL then '0' else unliquidated_poundage_price_temp END unliquidated_poundage_price_temp," +
                " CASE WHEN unliquidated_price =null then '0' else unliquidated_price END unliquidated_price" +
                " FROM" +
                " t_insurance_company_bill_balance" +
                " ) temp where  temp.bill_id=? and temp.unliquidated_poundage_price<=0 group by temp.bill_id) " +
                " balance " +
                " set bill.adjust_price_loss=balance.unliquidated_poundage_price " +
                " where bill.id=balance.bill_id and bill.id=?");
        this.billDao.executeSQL(sqlLoss.toString(),billId,billId);
    }

    /**
     * 调整佣金
     * @param data
     * @param billId
     * @throws Exception
     */
    private void diffBalanceDetailAdjustByCommission(Map data,String billId,TInsuranceCompanyBillAdjustLog adjustLog) throws Exception {
        //调整佣金
        //判断是否填写佣金的调整金额
        String amount=StringUtil.formatEmpty(data.get("amounts"));
        String amountss=StringUtil.formatEmpty(data.get("amountss"));
        if("".equals(amount) || "".equals(amountss)){
            throw new Exception("请提供调整佣金金额数额!!");
        }
        String exp=StringUtil.formatEmpty(data.get("adjustExp"))+"  [佣金差额:"+amount+" 至 "+amountss+"￥]";
        Float amountF=Float.valueOf(amount);
        Float amountssF=Float.valueOf(amountss);
        if(amount==amountss){
            throw new Exception("请调整佣金金额区间值!!");
        }
        adjustLog.setAmount(amount+"-"+amountss);
        adjustLog.setAdjustExp(exp);
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        sql.append("update t_insurance_company_bill_balance balance,( " +
                " select t.id itemId,detail.id detailId, " +
                " ROUND((detail.poundage_price-t.commission_price),2) unliquidated_poundage_price, " +
                " ROUND((detail.price-t.price),2) unliquidated_price, " +
                " ROUND((detail.poundage-t.commission),2) unliquidated_poundage " +
                " from t_insurance_order_item t,t_insurance_company_bill_detail detail WHERE " +
                " detail.blance_type = ? ");
        params.add(Constant.INSURANCE_BLANCE_TYPE.init.ordinal());
        sql.append(
                " AND t.contract_type = detail.contract_type " +
                " AND t.contract_no = detail.contract_no " +
                " AND t.insurance = detail.insurance AND ABS(detail.poundage_price-t.commission_price)<=ABS(?) " +
                        "AND ABS(detail.poundage_price-t.commission_price)>=ABS(?) and detail.bill_id=? ) temp ");
        params.add(amountssF);
        params.add(amountF);
        params.add(billId);
        sql.append(
                " set balance.adjust_reason='yj',balance.unliquidated_poundage_price=temp.unliquidated_poundage_price, " +
                " balance.unliquidated_poundage_price_temp=temp.unliquidated_poundage_price," +
                " balance.unliquidated_poundage=temp.unliquidated_poundage,balance.unliquidated_price=temp.unliquidated_price, " +
                " balance.adjust_exp=?,balance.blance_type=?,balance.blance_date=DATE_FORMAT(NOW(),'%Y-%m-%d')," +
                " balance.detail_id=temp.detailId " +
                " where balance.item_id=temp.itemId and balance.bill_id=? ");
        params.add(exp);
        params.add(Constant.INSURANCE_BLANCE_TYPE.blance.ordinal());
        params.add(billId);
        int result=this.billDao.executeSQL(sql.toString(),params.toArray());
        adjustLog.setResult(String.valueOf(result));
    }

    /**
     * 调整佣金率
     * @param data
     * @param billId
     * @throws Exception
     */
    private void diffBalanceDetailAdjustByCommissionRate(Map data,String billId,TInsuranceCompanyBillAdjustLog adjustLog) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<Object> params=new ArrayList<>();
        //填写原因
        String exp=StringUtil.formatEmpty(data.get("adjustExp"));
        if("".equals(StringUtil.formatEmpty(data.get("amount")))){
            throw new Exception("未提供佣金费率信息!!");
        }
        Float amount=Float.valueOf(StringUtil.formatEmpty(data.get("amount")));
        adjustLog.setAmount(String.valueOf(amount));
        adjustLog.setAdjustExp(exp);
        sql.append(" update t_insurance_company_bill_balance balance,( " +
                " select t.id itemId,detail.id detailId, " +
                " ROUND((detail.poundage_price-t.commission_price),2) unliquidated_poundage_price, " +
                " ROUND((detail.price-t.price),2) unliquidated_price, " +
                " ROUND((detail.poundage-t.commission),2) unliquidated_poundage " +
                " from (select id,CASE is_commission_temp WHEN 'true' THEN commission_temp ELSE commission END commission, " +
                " price,commission_price,contract_type, contract_no, insurance from t_insurance_order_item ) t,t_insurance_company_bill_detail detail WHERE " +
                " detail.blance_type = ? ");
        params.add(Constant.INSURANCE_BLANCE_TYPE.init.ordinal());
        sql.append(
                " AND t.contract_type = detail.contract_type " +
                " AND t.contract_no = detail.contract_no" +
                " AND t.insurance = detail.insurance " +
                " AND t.price = detail.price" +
                " and detail.bill_id=?) temp");
        params.add(billId);
        sql.append(
                " set balance.adjust_reason='yjl',balance.unliquidated_poundage_price=temp.unliquidated_poundage_price, " +
                " balance.unliquidated_poundage_price_temp=temp.unliquidated_poundage_price," +
                " balance.unliquidated_poundage=temp.unliquidated_poundage,balance.unliquidated_price=temp.unliquidated_price, " +
                " balance.adjust_exp=?,balance.blance_type=?,balance.blance_date=DATE_FORMAT(NOW(),'%Y-%m-%d'), " +
                " balance.detail_id=temp.detailId " +
                " where balance.item_id=temp.itemId and balance.bill_id=? " +
                " and ABS(temp.unliquidated_poundage)=ABS(?) ");
        params.add(exp);
        params.add(Constant.INSURANCE_BLANCE_TYPE.blance.ordinal());
        params.add(billId);
        params.add(amount);
        int result=this.billDao.executeSQL(sql.toString(),params.toArray());
        adjustLog.setResult(String.valueOf(result));
    }

    /**
     * 导出差异明细Excel
     * @param uid
     * @param billId
     * @param data
     * @return
     * @throws Exception
     */
    @Override
    public XSSFWorkbook diffExpExcel(String uid,String billId,Map data) throws Exception {
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        this.diffDetailQueryToSql(sql,params,billId,uid,data);
        List<Map<String,Object>> list=this.billDao.list(sql.toString(),params.toArray());
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("结算管理--差异明细详情");
        XSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        //合并单元格
        CellRangeAddress cell1=new CellRangeAddress(0,0,0,4);
        CellRangeAddress cell2=new CellRangeAddress(0,0,5,10);
        CellRangeAddress cell3=new CellRangeAddress(0,0,11,14);
        CellRangeAddress cell4=new CellRangeAddress(0,0,15,16);
        sheet.addMergedRegion(cell1);
        sheet.addMergedRegion(cell2);
        sheet.addMergedRegion(cell3);
        sheet.addMergedRegion(cell4);
        Row row1 = sheet.createRow(0);
        Cell headCell=row1.createCell(0);
        headCell.setCellValue("保险公司信息");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(5);
        headCell.setCellValue("4s店信息");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(11);
        headCell.setCellValue("结算信息");
        headCell.setCellStyle(cellStyle);

        headCell=row1.createCell(15);
        headCell.setCellValue("结算调整信息");
        headCell.setCellStyle(cellStyle);

        String[] excelHeaderRecordout = {"保单号", "被保险人", "保险金额", "佣金率", "佣金", "保单号", "被保险人", "保险金额",
                "佣金率","临时佣金", "佣金","结算佣金","结算金额","未结算佣金","未结算金额","调整因素","调整说明"};
        XSSFRow row = sheet.createRow((int) 1);


        for (int i = 0; i < excelHeaderRecordout.length; i++) {
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(excelHeaderRecordout[i]);
            cell.setCellStyle(cellStyle);
            sheet.autoSizeColumn(i);
//            sheet.setDefaultRowHeightInPoints(24);
//            sheet.setColumnWidth(cell.getColumnIndex(), 256 * 30);
        }
        for (int i = 0; i < list.size(); i++) {
            Map<String,Object> map=list.get(i);
            row = sheet.createRow(i + 2);
            Float detailPrice=0F;
            row.createCell(0).setCellValue(StringUtil.formatEmpty(map.get("detailContractNo")));
            row.createCell(1).setCellValue(StringUtil.formatEmpty(map.get("detailInsurance")));
            row.createCell(2).setCellValue(StringUtil.formatEmpty(map.get("detailPrice")));
            row.createCell(3).setCellValue("".endsWith(StringUtil.formatEmpty(map.get("detailPoundage")))?"":StringUtil.formatEmpty(map.get("detailPoundage"))+"%");
            row.createCell(4).setCellValue(StringUtil.formatEmpty(map.get("detailPoundagePrice")));
            row.createCell(5).setCellValue(StringUtil.formatEmpty(map.get("itemContractNo")));
            row.createCell(6).setCellValue(StringUtil.formatEmpty(map.get("itemInsurance")));
            row.createCell(7).setCellValue(StringUtil.formatEmpty(map.get("itemPrice")));
            row.createCell(8).setCellValue("".endsWith(StringUtil.formatEmpty(map.get("itemPoundage")))?"":StringUtil.formatEmpty(map.get("itemPoundage"))+"%");
            row.createCell(9).setCellValue("".endsWith(StringUtil.formatEmpty(map.get("itemPoundageTemp")))?"":StringUtil.formatEmpty(map.get("itemPoundageTemp"))+"%");
            row.createCell(10).setCellValue(StringUtil.formatEmpty(map.get("itemPoundagePrice")));
            row.createCell(11).setCellValue(StringUtil.formatEmpty(map.get("detailPoundagePrice")));
            row.createCell(12).setCellValue(StringUtil.formatEmpty(map.get("detailPrice")));
            row.createCell(13).setCellValue(StringUtil.formatEmpty(map.get("unliquidatedPoundagePrice")));
            row.createCell(14).setCellValue(StringUtil.formatEmpty(map.get("unliquidatedPrice")));
            String adjust="";
            if(!"".equals(StringUtil.formatEmpty(map.get("adjustReason")))){
                adjust=StringUtil.formatEmpty(map.get("adjustReason")).equals(ADJUST.YJ.name().toLowerCase())?"佣金":"佣金率";
            }
            row.createCell(15).setCellValue(adjust);
            row.createCell(16).setCellValue(StringUtil.formatEmpty(map.get("adjustExp")));
        }
        return wb;
    }


    /**
     * 录入对账单开票信息
     * @param billid
     * @param billingDate
     * @param billingPrice
     * @param invoiceNumber
     * @throws Exception
     */
    @Override
    public void billing(String billid,String billingDate,String billingPrice,String invoiceNumber) throws Exception {
        if("".equals(billid)){
            throw new Exception("请选择对账单!!");
        }
        //获取对账单
        TInsuranceCompanyBill bill=this.billDao.getInfo(billid);
        if(bill==null){
            throw new Exception("未查询到对账单信息，请调整后重试!!");
        }
        if(bill.getBlanceType().equals(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.init.ordinal()))){
            throw new Exception("该对账单尚未结平，无法录入发票信息!!");
        }
        if(!"".equals(bill.getInvoiceNumber())){
            throw new Exception("该对账单已经开票!!");
        }
        bill.setBillingDate(billingDate);
        bill.setBillingPrice(billingPrice);
        bill.setInvoiceNumber(invoiceNumber);
        bill.setBlanceType(String.valueOf(Constant.INSURANCE_BLANCE_TYPE.billing.ordinal()));
        this.billDao.saveOrUpdate(bill);
    }


    /**
     * 1、添加保单至结算单
     * 2、结算保险单
     * @param uid
     * @param billId
     * @param data
     * @throws Exception
     */
    @Override
    public void addInsuranctOrderToBalance(String uid,String billId,List<Map> data) throws Exception {
        TInsuranceCompanyBill bill=this.billDao.getInfo(billId);
        if(data.size()==0){
            throw new Exception("请选择要添加的保单!!");
        }
        StringBuffer sql=new StringBuffer();
        List<String> params=new ArrayList<>();
        sql.append(" insert into T_INSURANCE_COMPANY_BILL_BALANCE(`id`, `create_time`, `create_user`,`bill_id`,`item_id`,`blance_type`,`insurance_type`) ");
        sql.append(" SELECT  replace(t.blanceId,'-',''),'"+System.currentTimeMillis()+"','"+uid+"','"+billId+"',t.id,t.blance_type,t.contract_type  FROM  ( " +
                "  SELECT UUID() blanceId,item.*, DATE_FORMAT(o.out_order_date, '%Y-%m-%d') out_order_date " +
                "  FROM t_insurance_order_item item  LEFT JOIN t_insurance_order o ON item.order_id = o.id  ");
        sql.append(" where item.blance_type='0' ");
        sql.append(" and item.id in ( ");
        for(int i=0;i<data.size();i++){
            sql.append("?");
            params.add(StringUtil.formatEmpty(data.get(i).get("id")));
            if(i<(data.size()-1)){
                sql.append(" , ");
            }
        }
        sql.append(" ) and not EXISTS (select * from T_INSURANCE_COMPANY_BILL_BALANCE where item_id=item.id ) ");
        sql.append(" ) t ");
        this.billDao.executeSQL(sql.toString(),params.toArray());
        //2、根据保单号、保费、被保险人、佣金与结算单的保单进行对比 如一致 则更新结算单
        this.blanceInsuaranceOrder(bill);
        //3、更新结算单，列入保险公司保单ID、更新结算状态；同步更新4s店保单、保险公司上传保单结算状态；
        //更新对账单
        this.updBillBlance(billId);
        //更新结算佣金
        this.upBillBlancePougdageAmount(billId);
    }


    /***
     * 解析 Excel
     * @param file
     * @param scheme
     * @return
     */
    @Override
    public int[] parseExcelByBalance(MultipartFile file, ExcelSchemeVo scheme, String uid,String billId,String adjustType) throws Exception {
        TInsuranceCompanyBill bill=this.billDao.getInfo(billId);
        //获取公司信息
        TInsuranceCompany company=companyService.getInfo(bill.getInsuranceCompanyId());
        String jqNo=company.getContractJqNO();
        String syNo=company.getContractSyNO();
        String fileName = file.getOriginalFilename();
        String type = fileName.substring(fileName.lastIndexOf("."));
        String companyId = TokenUtil.getCompanyId(uid);
        TInsuranceCompanyBillBalanceLog adjustLog=this.saveAdjustLog(bill,adjustType,uid);
        if (type != null && (type.endsWith("xlsx") || type.endsWith("xls"))) {
            //2007之后版本
            int successNum[] = {0, 0};
            try {
                ExcelWorkSheetRowCallbackHandler sheetRowCallbackHandler =
                        new ExcelWorkSheetRowCallbackHandler(new ExcelRowContentCallback() {
                            @Override
                            public void processRow(int rowNum, Map<String, String> map) throws Exception {
                                if (scheme.getDataStartRow() - 1 <= rowNum && null != map) {
                                    BaseEntity be = EntityFactory.BuildEntity(scheme, new ArrayList(map.values()), uid, companyId,null);
                                    if (null != be) {
                                        successNum[0]++;
                                        TInsuranceCompanyBillDetail detail=(TInsuranceCompanyBillDetail)be;

                                        if(!StringUtil.formatEmpty(detail.getContractNo()).equals("")) {
                                            if (detail.getContractNo().indexOf(jqNo) != -1 || detail.getContractType().indexOf("交强") != -1) {
                                                detail.setContractType("jq");
                                            }
                                            if (detail.getContractNo().indexOf(syNo) != -1 || detail.getContractType().indexOf("商业") != -1) {
                                                detail.setContractType("sy");
                                            }
                                            detail.setBillId(billId);
                                            if (detail.getPoundage().indexOf("%") != -1) {
                                                detail.setPoundage(detail.getPoundage().replaceAll("%", ""));
                                            }
                                            Float poundage = Float.valueOf(detail.getPoundage());
                                            if (poundage < 1 && poundage > 0) {
                                                detail.setPoundage(String.valueOf(poundage * 100));
                                            }
                                            detail.setInsuranceCompanyId(bill.getInsuranceCompanyId());
                                            detail.setBlanceType("0");
                                            TInsuranceCompanyBillBalanceDetail balanceDetail=new TInsuranceCompanyBillBalanceDetail();
                                            BeanUtils.copyProperties(detail,balanceDetail);
                                            balanceDetail.setAdjustId(adjustLog.getId());
                                            billDao.saveOrUpdate(balanceDetail);
                                        }
                                    }
                                }
                            }
                        });
                sheetRowCallbackHandler.setHeadRow(scheme.getTitleRow() - 1);
                PoiExcelUtil.readExcel(type, file.getInputStream(), 0, sheetRowCallbackHandler, null);
                diffBillAdjust(adjustLog,bill);
            } catch (RuntimeException are) {
                are.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            }
            return successNum;

        } else {
            throw new Exception("无法识别的文件格式！");
        }
    }

    private TInsuranceCompanyBillBalanceLog saveAdjustLog(TInsuranceCompanyBill bill,String adjustType,String uid) throws Exception {
        TInsuranceCompanyBillBalanceLog log=new TInsuranceCompanyBillBalanceLog();
        log.setCreateTime(System.currentTimeMillis());
        log.setCreateUser(uid);
        log.setBillId(bill.getId());
        log.setAdjustType(adjustType);
        log.setAdjustPrice(bill.getAdjustPrice());
        return (TInsuranceCompanyBillBalanceLog) this.billDao.saveOrUpdate(log);
    }

    private void diffBillAdjust(TInsuranceCompanyBillBalanceLog adjustLog,TInsuranceCompanyBill bill) throws Exception {
        //1、获取本次上传保单的总佣金金额
        Float price=0F;
        //要调整的金额必须为小于0的 说明是保险公司欠4s店的钱
        Map map=billDao.getAdjustByDetail(adjustLog.getId(),adjustLog.getBillId());
        if(map!=null){
            if(map.size()>0){
                price=Math.abs(Float.valueOf(StringUtil.formatEmpty(map.get("poundagePrices"))));
            }
        }
        //2、设置日志本次上传总金额
        adjustLog.setPrice(price.toString());
        this.billDao.saveOrUpdate(adjustLog);
        //3、判断是否大于等于待调整金额
        Float adjustPrice=Math.abs(Float.valueOf(bill.getAdjustPrice()));
        if(price>=adjustPrice){
            //直接设置待调整金额为0
            bill.setAdjustPrice("0");
            billDao.saveOrUpdate(bill);
            //清空所属结算单下的差异佣金
            String sql="update t_insurance_company_bill_balance set unliquidated_poundage_price=0 where bill_id=? and unliquidated_poundage_price<0";
            this.billDao.executeSQL(sql,bill.getId());
        }else{
            this.diffBalanceBillByPrice(bill,price);
        }
        this.diffBalanceByAdjustPrice(bill.getId());
    }

    /**
     * 根据对账单、平账金额 对对对账单进行平账
     * @param bill
     * @param price
     * @return
     * @throws Exception
     */
    @Override
    public Float diffBalanceBillByPrice(TInsuranceCompanyBill bill,Float price) throws Exception {
        //4、当金额小于的时候
        while (true){
            //只查找差异金额小于0的数据
            String sql="SELECT * FROM t_insurance_company_bill_balance WHERE unliquidated_poundage_price<0 and bill_id=? limit 0,100";
            List<Map<String,Object>> list=this.billDao.list(sql,bill.getId());
            if(list.size()==0){
                break;
            }
            if(price<=0){
                break;
            }
            List<String> idList=new ArrayList<>();
            Map unliquidatedMap=null;
            for(int i=0;i<list.size();i++){
                Map<String,Object> infoMap=list.get(i);
                Float unPrice=Float.valueOf(StringUtil.formatEmpty(infoMap.get("unliquidatedPoundagePrice")));
                //计算差额的绝对值
                unPrice=Math.abs(unPrice);
                if(price>=unPrice){
                    price=price-unPrice;
                    idList.add(StringUtil.formatEmpty(infoMap.get("id")));
                }else{
                    //当金额 不足时，只能调整一条记录时
                    Float un=(unPrice-price);
                    un=un*-1;
                    unliquidatedMap=new HashMap();
                    unliquidatedMap.put("id",StringUtil.formatEmpty(infoMap.get("id")));
                    unliquidatedMap.put("price",un);
                    price=0f;
                    break;
                }
            }
            //执行更新操作
            if(idList.size()>0){
                StringBuffer sqlUpd=new StringBuffer();
                sqlUpd.append(" update t_insurance_company_bill_balance set unliquidated_poundage_price=0 where id in ( ");
                for (int i=0;i<idList.size();i++){
                    sqlUpd.append("?");
                    if(i<(idList.size()-1)){
                        sqlUpd.append(",");
                    }
                }
                sqlUpd.append(" ) ");
                this.billDao.executeSQL(sqlUpd.toString(),idList.toArray());
            }
            //判断未结算完成的是否有值
            if(unliquidatedMap!=null){
                String sqlUn=" update t_insurance_company_bill_balance set unliquidated_poundage_price=? where id=?";
                this.billDao.executeSQL(sqlUn,unliquidatedMap.get("price"),unliquidatedMap.get("id"));
            }
        }
        return price;
    }

    /**
     * 根据发票号批量进行结算
     * @param data
     * @param invoiceNum
     * @param invoiceDate
     * @param invoicePrice
     * @param uid
     * @param adjustType
     * @throws Exception
     */
    @Override
    public void diffBalanceBillByDateList(List<Map> data,String invoiceNum,String invoiceDate,String invoicePrice,String uid,String adjustType) throws Exception {
        if(data.size()==0){
            throw new Exception("请选择要平账的对账单!!");
        }
        //检测发票号在结平日志中是否已经存在 ，如果存在则不无话继续使用
        boolean flag=this.checkBlanceLogByInvoiceNum(invoiceNum);
        if(flag){
            throw new Exception("该发票号已经存在，不允许在次应用于对账结平!!");
        }
        Float price=Float.valueOf(invoicePrice);
        for(int i=0;i<data.size();i++){
            String id=StringUtil.formatEmpty(data.get(i).get("id"));
            if(!"".equals(id)){
                TInsuranceCompanyBill bill=this.billDao.getInfo(id);
                TInsuranceCompanyBillBalanceLog log=new TInsuranceCompanyBillBalanceLog();
                log.setCreateTime(System.currentTimeMillis());
                log.setCreateUser(uid);
                log.setBillId(bill.getId());
                log.setAdjustType(adjustType);
                log.setAdjustPrice(bill.getAdjustPrice());
                log.setPrice(invoicePrice);
                log.setInvoiceDate(invoiceDate);
                log.setInvoiceNum(invoiceNum);
                this.billDao.saveOrUpdate(log);
                price=this.diffBalanceBillByPrice(bill,price);
            }
            this.diffBalanceByAdjustPrice(id);
        }
    }

    /**
     * 检测发票是否已经存在 ，如果存在 返回true则不允许结平
     * @param invoiceNum
     * @return
     * @throws Exception
     */
    private boolean checkBlanceLogByInvoiceNum(String invoiceNum) throws Exception {
        String sql="select * from T_INSURANCE_COMPANY_BILL_BALANCE_LOG where invoice_num=?";
        List list=this.billDao.list(sql,invoiceNum);
        if(list.size()>0){
            return true;
        }else{
            return false;
        }
    }

    /**
     * 结平日志
     * @param billId
     * @param start
     * @param limit
     * @return
     * @throws Exception
     */
    @Override
    public Map findBalanceAdjustLogs(String billId,String start,String limit) throws Exception {
        String sql="select * from t_insurance_company_bill_balance_log where bill_id=?";
        return this.billDao.page(sql,new String[]{billId},start,limit);
    }

    /**
     * 调整保险公司保费及佣金，使其与4s店填写一致，便于后期对账
     * @param billid
     * @param data
     * @throws Exception
     */
    @Override
    public void diffBalanceInsuranceCom(String billid,String uid,Map<String,Object> data) throws Exception {
        TInsuranceCompanyBill bill=this.billDao.getInfo(billid);
        String contractType=StringUtil.formatEmpty(data.get("contractType"));
        if("".equals(contractType)){
            throw new Exception("请选择商业或交强险!!");
        }
        diffBalanceInsuranceComByPrice(bill,data,contractType,uid);
        diffBalanceInsuranceComByCom(bill,data,contractType,uid);

    }

    /**
     * 保费调整
     * @param bill
     * @param data
     * @param contractType
     * @param uid
     * @throws Exception
     */
    private void diffBalanceInsuranceComByPrice(TInsuranceCompanyBill bill,Map<String,Object> data,String contractType,
                                                String uid)throws Exception {
        Integer result=0;
        TInsuranceCompanyBillAdjustLog log=new TInsuranceCompanyBillAdjustLog();
        log.setBillId(bill.getId());
        //保费调整类型
        String priceAdjustType=StringUtil.formatEmpty(data.get("priceAdjustType"));
        String priceAdjustValue="".endsWith(StringUtil.formatEmpty(data.get("priceAdjustValue")))?"0":StringUtil.formatEmpty(data.get("priceAdjustValue"));
        if(!"0".equals(priceAdjustValue)){
            StringBuffer sql=new StringBuffer();
            List<String> params=new ArrayList<>();
            sql.append(" update t_insurance_company_bill_detail set bill_id=bill_id ");
            Float priceAdjustValueF=Float.valueOf(priceAdjustValue);
            //判断是否大于含税金额
            if(priceAdjustValueF>Float.valueOf(bill.getTax())){
                throw new Exception("不应大于账单的税点!!");
            }
            //调整对账单的保费
            if("1".equals(priceAdjustType)) {
                log.setAdjustExp("保险公司保费上浮"+priceAdjustValueF);
                sql.append(" ,price=convert(price*'"+priceAdjustValueF+"',decimal(65,2)) ");
            }else{
                log.setAdjustExp("保险公司保费下浮"+priceAdjustValueF);
                sql.append(" ,price=convert(price/'"+priceAdjustValueF+"',decimal(65,2)) ");
            }
            log.setType(priceAdjustType);
            log.setAdjustReason("保险公司保费");
            log.setAmount(priceAdjustValueF.toString());
            log.setContractType(contractType);
            boolean flag=this.checkDiffBalanceInsuranceCom(log);
            if(!flag) {
                result = diffBalanceInsuranceComToSqlAndExec(sql, params, bill, contractType);
            }else{
                throw new Exception("请查看日志，已经进行过相关调整!!");
            }
            log.setCreateTime(System.currentTimeMillis());
            log.setResult(result.toString());
            log.setCreateUser(uid);
            this.billDao.saveOrUpdate(log);
        }
    }

    /**
     * 佣金费率调整
     * @param bill
     * @param data
     * @param contractType
     * @param uid
     * @throws Exception
     */
    private void diffBalanceInsuranceComByCom(TInsuranceCompanyBill bill,Map<String,Object> data,String contractType,
                                              String uid) throws Exception {
        Integer result=0;
        TInsuranceCompanyBillAdjustLog log=new TInsuranceCompanyBillAdjustLog();
        log.setBillId(bill.getId());
        String comceAdjustType=StringUtil.formatEmpty(data.get("comAdjustType"));
        String comAdjustValue="".endsWith(StringUtil.formatEmpty(data.get("comAdjustValue")))?"0":StringUtil.formatEmpty(data.get("comAdjustValue"));
        if(!"0".equals(comAdjustValue)){
            StringBuffer sql=new StringBuffer();
            List<String> params=new ArrayList<>();
            sql.append(" update t_insurance_company_bill_detail set bill_id=bill_id ");
            Float comAdjustValueF=Float.valueOf(comAdjustValue);
            //判断是否大于含税金额
            if(comAdjustValueF>Float.valueOf(bill.getTax())){
                throw new Exception("不应大于账单的税点!!");
            }
            StringBuffer sqlPrice=new StringBuffer();
            sqlPrice.append(sql.toString());
            if("1".equals(comceAdjustType)) {
                log.setAdjustExp("保险公司佣金费率上浮"+comAdjustValueF);
                sql.append(" ,poundage=convert(poundage*"+comAdjustValueF+",DECIMAL (65, 2)) ");
                sqlPrice.append(" ,poundage_price=convert(price*(poundage/100),decimal(65,2))");
            }else{
                log.setAdjustExp("保险公司佣金费率下浮"+comAdjustValueF);
                sql.append(" ,poundage=convert(poundage/"+comAdjustValueF+",DECIMAL (65, 2)) ");
                sqlPrice.append(" ,poundage_price=convert(price*(poundage/100),decimal(65,2))");
            }
            log.setAdjustReason("保险公司佣金费率");
            log.setAmount(comAdjustValueF.toString());
            log.setType(comceAdjustType);
            log.setContractType(contractType);
            boolean flag=this.checkDiffBalanceInsuranceCom(log);
            if(!flag) {
                result = diffBalanceInsuranceComToSqlAndExec(sql, params, bill, contractType);
                params.clear();
                result = diffBalanceInsuranceComToSqlAndExec(sqlPrice, params, bill, contractType);
            }else{
                throw new Exception("请查看日志，已经进行过相关调整!!");
            }
            log.setCreateTime(System.currentTimeMillis());
            log.setResult(result.toString());
            log.setCreateUser(uid);
            this.billDao.saveOrUpdate(log);
        }
    }

    /**
     * 是否可继续调整
     * @param log
     * @return true已经调整过 false未调整过
     * @throws Exception
     */
    private boolean checkDiffBalanceInsuranceCom(TInsuranceCompanyBillAdjustLog log) throws Exception {
        List<Map<String, Object>> list=this.billDao.list("select * from T_INSURANCE_COMPANY_BILL_ADJUST_LOG where adjust_reason=? " +
                "and type=? and bill_id=? and contract_type=?",log.getAdjustReason(),log.getType(),log.getBillId(),log.getContractType());
        if(list.size()>0){
            return true;
        }else{
            return false;
        }
    }
    private Integer diffBalanceInsuranceComToSqlAndExec(StringBuffer sql,List<String> params,TInsuranceCompanyBill bill,
                                                    String contractType) throws Exception {
        sql.append(" where bill_id=? and contract_type=? ");
        params.add(bill.getId());
        params.add(contractType);
        return this.billDao.executeSQL(sql.toString(),params.toArray());
    }

    /**
     * 调整因素日志
     * @param billId
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String, Object>> diffBalanceInsuranceComLogs(String billId) throws Exception {
        return this.billDao.list(" select CONCAT(u.u_name,'于',FROM_UNIXTIME(log.create_time/1000,'%Y-%m-%d')," +
                " '调整',CASE WHEN contract_type='jq' THEN '交强险;' WHEN contract_type='sy' THEN '商业险;' else ';' END," +
                " log.adjust_reason,';备注:',log.adjust_exp,';',log.amount,';') msg from  " +
                " t_insurance_company_bill_adjust_log log,t_bc_user u where log.create_user=u.id " +
                " and log.bill_id=? order by log.create_time desc",billId);
    }

    /**
     * 获取对账单针对的公司信息
     * @param billId
     * @return
     * @throws Exception
     */
    @Override
    public List<Map<String,Object>> diffBalanceCompanys(String billId) throws Exception {
        String sql="select id,o_name from t_bc_organ where id in (select apply_company_id from t_insurance_company_bill_log where bill_id=?) group by id";
        return this.billDao.list(sql,billId);
    }
}
